package com.awakening.code.dao;

import com.awakening.code.bean.Message;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 和Message表相关的数据库操作
 * @Author:AwakeningCode
 * @Date: Created in 21:17 2018\1\11 0011
 */
public class MessageDao {

    /**
     * 根据查询条件查询消息列表
     * @param command
     * @param description
     * @return
     * @throws Exception
     */
    public List<Message> findList(String command,String description)throws ClassNotFoundException,SQLException{
        List<Message> messageList = new ArrayList<Message>();
        //JDBC经典流程
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.01:3306/mybatis?characterEncoding=utf8","root","root");
        StringBuilder sql = new StringBuilder("select id,command,description,content from message where 1 = 1");
        //定义一个默认的存储类型，放入条件值
        List<String> paramList = new ArrayList<String>();
        //默认判断是否进行条件查询、初始化页面状态
        if(command != null && !"".equals(command.trim())){
            //注意and之前是由空格的
            sql.append(" and command=?");
            paramList.add(command);
        }
        if(description != null && !"".equals(description.trim())){
            //注意空格模糊查询
            sql.append(" and description like '%' ? '%'");
            paramList.add(description);
        }
        PreparedStatement statement = conn.prepareStatement(sql.toString());
        for (int i = 0;i<paramList.size();i++){
            statement.setString(i+1,paramList.get(i));
        }
        ResultSet rs = statement.executeQuery();
        while (rs.next()){
            Message message = new Message();
            messageList.add(message);
            message.setId(rs.getString("id"));
            message.setCommand(rs.getString("command"));
            message.setDescription(rs.getString("description"));
            message.setContent(rs.getString("content"));
        }
        return messageList;
    }

}
